﻿package framework.core;

import java.sql.*;

/**
 * Creation date: (2003-08-28)
 * @author: adminnistrator
 * 数据库工具
 */

public class DBEngine 
{
    public static void checkCon(DBConnection con) throws Exception
    {
        if(con==null || con.isClosed())
        {
            Logger.throwException("DBEngine","数据库连接不可以为空"+con);
        }
    }
    
    //select *
    public static DBResult doSelect(DBConnection con,String sql) throws Exception
    {
        checkCon(con);
        DBPreparedStatement pst = con.prepareStatement(sql);
        java.sql.ResultSet rs = pst.executeQuery();
        return new DBResult(pst,rs);
    }
    
    //select *
    public static DBResult doSelect(DBConnection con,String sql,Conditions conditions) throws Exception
    {
        return doSelect(con,sql,conditions,"");
    }
    
    //select + order by
    public static DBResult doSelect(DBConnection con,String sql,Conditions conditions,String sort) throws Exception
    {
        checkCon(con);

        sql += conditions.getSql();

        if(sort!=null && !sort.equals(""))
        {
            sql += " order by " + sort ;
        }
        
        DBPreparedStatement pst = con.prepareStatement(sql);
        
        for(int i=0;i<conditions.getParamCount();i++)
        {
            pst.setParam(i+1,conditions.getParam(i));
        }
        java.sql.ResultSet rs = pst.executeQuery() ;
        return new DBResult(pst,rs);
    }
        
    //select * + order by 
    public static DBResult doSelect(DBConnection con,String sql,Conditions conditions,String orderField,boolean desc) throws Exception
    {
        String sort = null;
        
        if(orderField!=null && !orderField.equals(""))
        {
            if(desc)
            {
                sort = orderField + " desc " ; 
            }
            else
            {
                sort = orderField ; 
            }
        }
        return doSelect(con,sql,conditions,sort);
    }
    
       public static DBResult doSelect(DBConnection con,String sql,Conditions conditions,SearchEvent event) throws Exception
        {
           checkCon(con);
           
            String sort = null;
            
            String tOrderField =event.getOrderField();
            boolean tDesc =event.isOrderDesc();
            
            if(tOrderField!=null && !tOrderField.equals(""))
            {
                if(tDesc)
                {
                    sort = tOrderField + " desc " ; 
                }
                else
                {
                    sort = tOrderField + " asc "; 
                }
            }

            sql += conditions.getSql();

            if(sort!=null && !sort.equals(""))
            {
                sql += " order by " + sort ;
            }
             
            if(event.getPageSize()!= Value.NULL_INT && event.getPageNum()!= Value.NULL_INT) {
                int tStartRecord =(event.getPageNum()-1)*event.getPageSize();
                int tEndRecord   =(event.getPageNum())*event.getPageSize()+1;
                sql = "select * from (select gta.*,rownum r from ("+sql+") gta) gtb where gtb.r >"+tStartRecord+" and gtb.r<"+tEndRecord;
            }
            
            DBPreparedStatement pst = con.prepareStatement(sql);
            
            for(int i=0;i<conditions.getParamCount();i++)
            {
                pst.setParam(i+1,conditions.getParam(i));
            }
            java.sql.ResultSet rs = pst.executeQuery() ;
            return new DBResult(pst,rs);
        }
       
       public static DBResult doSelectWithOutOrder(DBConnection con,String sql,SearchEvent event) throws Exception
       {
          checkCon(con);

           if(event.getPageSize()!= Value.NULL_INT && event.getPageNum()!= Value.NULL_INT) {
               int tStartRecord =(event.getPageNum()-1)*event.getPageSize();
               int tEndRecord   =(event.getPageNum())*event.getPageSize()+1;
               sql = "select * from (select gta.*,rownum r from ("+sql+") gta) gtb where gtb.r >"+tStartRecord+" and gtb.r<"+tEndRecord;
           }
           
           DBPreparedStatement pst = con.prepareStatement(sql);

           java.sql.ResultSet rs = pst.executeQuery() ;
           return new DBResult(pst,rs);
       }
    
    //select count(*)
    public static int doCount(DBConnection con,String sql) throws Exception
    {
        checkCon(con);
        DBResult result = doSelect(con,sql);
        int count = 0 ;
        java.sql.ResultSet rs = result.getRs();
        if(rs.next())
        {
            count = rs.getInt(1);
        }
        result.clear();
        return count;
    }
    
    //select count(*)
    public static int doCount(DBConnection con, String sql,
            Conditions conditions) throws Exception {

        DBPreparedStatement pst = null;
        java.sql.ResultSet rs = null;
        try {
            checkCon(con);
            sql += conditions.getSql();            
            pst = con.prepareStatement(sql);
            for (int i = 0; i < conditions.getParamCount(); i++) {
                pst.setParam(i + 1, conditions.getParam(i));
            }
            rs = pst.executeQuery();
            int count = 0;
            if (rs.next()) {
                count = rs.getInt(1);
            }

            return count;
        } finally {
            if (rs != null)
                rs.close();
            if (pst != null)
                pst.close();
        }
    }
    public static int doCount(DBConnection con,String sql,Values values) throws Exception
    {
        ObjectList list = null;
        checkCon(con);
        DBPreparedStatement pst = con.prepareStatement(sql);
        for(int i=0;i<values.size();i++)
        {
            pst.setParam(i+1,values.get(i));
        }
        java.sql.ResultSet rs = pst.executeQuery() ;
        int count = 0 ;
        if(rs.next())
        {
            count = rs.getInt(1);
        }
        rs.close();
        pst.close();
        return count;
    }
    
    public static int doTableCount(DBConnection con,String table,Conditions conditions) throws Exception
    {
        String sql = "select count(*) from " + table ;
        return doCount(con,sql,conditions);
    }
    
    //sum
    public static double doSum(DBConnection con,String sql,Conditions conditions) throws Exception
    {
        DBEngine.checkCon(con);
        DBResult result = DBEngine.doSelect(con,sql,conditions);
        ResultSet rs = result.getRs();
        rs.next();
        double sum = rs.getDouble(1);
        result.clear();
        return sum;
    }

    //update 
    public static void doUpdate(DBConnection con, String sql) throws Exception {
        checkCon(con);
        DBPreparedStatement pst = null;

        try {
            pst = con.prepareStatement(sql);
            try {
                pst.executeUpdate();
            } catch (SQLException e) {
                if (e.getErrorCode() == Config.ORACEL_FK_ERROR_CODE) {
                    throw new BizException("数据已经被使用,无法删除!");
                }
                throw e;
            }
        } finally {
            if (pst != null)
                pst.close();
        }
    }
    
    //update
    public static void doUpdate(DBConnection con,String sql,Conditions conditions) throws Exception
    {
        checkCon(con);
        sql += conditions.getSql();
        DBPreparedStatement pst = null;

        try {
            pst = con.prepareStatement(sql);
            for (int i = 0; i < conditions.getParamCount(); i++) {
                pst.setParam(i + 1, conditions.getParam(i));
            }
            try {
                pst.executeUpdate();
            } catch (SQLException e) {
                if (e.getErrorCode() == Config.ORACEL_FK_ERROR_CODE) {
                    throw new BizException("数据已经被使用,无法删除!");
                }
                throw e;
            }
        } finally {
            if (pst != null)
                pst.close();
        }
    }
    
    //id util function
    public static synchronized int getId(DBConnection con,String seqName) throws Exception 
    {
        checkCon(con);
        DBResult result = null;
        try
        {
            result = DBEngine.doSelect(con,"select "+seqName+".nextval from dual");
            ResultSet rs = result.getRs();
            if (!rs.next()) 
            {
                Logger.throwException("DBEngine.getId()","序列号名称不存在:"+seqName);
            }
            int id = (int) rs.getLong(1);
            return id ;
        }
        finally
        {
            if(result!=null) result.clear() ;
        }
    }
    
    public static DBResult doSelect(DBConnection con,String sql,Values values) throws Exception
    {
        checkCon(con);
        DBPreparedStatement pst = con.prepareStatement(sql);
        for(int i=0;i<values.size();i++)
        {
            
            pst.setParam(i+1,values.get(i));
        }
        java.sql.ResultSet rs = pst.executeQuery() ;
        return new DBResult(pst,rs);
    }
    
    public static ObjectList doSelect(DBConnection con,String sql,Values values,Class c,int pageSize,int pageNo,int total) throws Exception
    {
        ObjectList list = null;
        checkCon(con);
        DBPreparedStatement pst = con.prepareStatement(sql);
        for(int i=0;i<values.size();i++)
        {
            pst.setParam(i+1,values.get(i));
        }
        java.sql.ResultSet rs = pst.executeQuery() ;
        list =  ObjectList.fromRs(c,rs,pageSize,pageNo,total);
        rs.close();
        
        return list;
    }
    
    public static ObjectList doSelect(DBConnection con,String sql,Values values,Class c,int pageSize,int pageNo) throws Exception
    {
        return doSelect(con,sql,values,c,pageSize,pageNo,-1);
    }   
    
    
    public static ObjectList doSelect(DBConnection con,String sql,Values values,Class c,SearchEvent event) throws Exception
    {
        return doSelect(con,sql,values,c,event,-1);
    }   
    
    public static ObjectList doSelect(DBConnection con,String sql,Values values,Class c,SearchEvent event,int total) throws Exception
    {
        if (!event.getOrderField().equalsIgnoreCase("ID"))
            sql += " order by " + event.getOrderField();
        if ((!event.getOrderField().equalsIgnoreCase("ID")) && (!event.getOrderField().equalsIgnoreCase("")) && (event.isOrderDesc()))
            sql += " desc";
                
        return doSelect(con,sql,values,c,event.getPageSize(),event.getPageNum(),total);
    }  

	public static synchronized String getId(DBConnection con) throws Exception 
	{
		checkCon(con);
		DBResult result = null;
		try
		{
			result = DBEngine.doSelect(con,"select SYS_GUID() from dual");
			ResultSet rs = result.getRs();
			if (!rs.next()) 
			{
				Logger.throwException("DBEngine.getId()","获取guid失败！");
			}
			String id =  rs.getString(1);
			return id ;
		}
		finally
		{
			if(result!=null) result.clear() ;
		}
	}
	
		
}
